﻿using BCCommon;
using BCEntity.Common.Banner;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;

namespace BCData.Common.Banner
{
    /// <summary>
    /// Banner分类数据处理
    /// </summary>
    public class BannerData : IBannerData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM banner";
        private const string QUERY = @"SELECT A.BannerId,A.Name,A.BackgroundImage,A.ForegroundImage,A.Title,A.Description,A.Url,A.BannerTagcode,A.Enabled,B.Description AS TagcodeDescription
                                        FROM Banner A
                                        INNER JOIN BannerTagcode B ON A.BannerTagcode=B.BannerTagcode";
        public BannerData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        public BannerEntity Add(BannerEntity bannerEntity)
        {
            string sql = @"INSERT INTO Banner(Name,BackgroundImage,ForegroundImage,Title,Description,Url,BannerTagcode)
                            VALUES(?Name,?BackgroundImage,?ForegroundImage,?Title,?Description,?Url,?BannerTagcode);
                            {0} WHERE A.BannerId=LAST_INSERT_ID();";
            sql = string.Format(sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Name", bannerEntity.Name, DbType.AnsiString, size: 50);
            parameters.Add("?BackgroundImage", bannerEntity.BackgroundImage, DbType.AnsiString, size: 1000);
            parameters.Add("?ForegroundImage", bannerEntity.ForegroundImage, DbType.AnsiString, size: 1000);
            parameters.Add("?Title", bannerEntity.Title, DbType.AnsiString, size: 20);
            parameters.Add("?Description", bannerEntity.Description, DbType.AnsiString, size: 100);
            parameters.Add("?Url", bannerEntity.Url, DbType.AnsiString, size: 1000);
            parameters.Add("?BannerTagcode", bannerEntity.BannerTagcode, DbType.AnsiString, size: 50);
            return this.databaseContext.Master.QueryFirstOrDefault<BannerEntity>(sql, parameters,databaseContext.Transaction);
        }

        public bool CheckBannerExists(long bannerId)
        {
            string sql = @"SELECT 1 FROM Banner WHERE BannerId=?BannerId LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BannerId", bannerId, DbType.Int64, size: 20);
            object result = this.databaseContext.Slave.ExecuteScalar(sql, parameters);
            return result != null;
        }

        public bool CheckBannerExists(string bannerName)
        {
            string sql = @"SELECT 1 FROM Banner WHERE Name=?Name LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Name", bannerName, DbType.AnsiString, size: 50);
            object result = this.databaseContext.Slave.ExecuteScalar(sql, parameters);
            return result != null;
        }

        public void Delete(long bannerId)
        {
            string sql = @"DELETE FROM Banner WHERE BannerId=?BannerId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BannerId", bannerId, DbType.Int64, size: 20);
            this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
        }

        public IEnumerable<BannerEntity> Get(string bannerTagcode)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            wheres.Add("A.Enabled=1");
            if (!string.IsNullOrWhiteSpace(bannerTagcode))
            {
                wheres.Add("A.BannerTagcode=?BannerTagcode");
                parameters.Add("?BannerTagcode", bannerTagcode, DbType.AnsiString, size: 50);
            }
            string sql = string.Format("{0} {1}", QUERY, wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : "");
            return this.databaseContext.Slave.Query<BannerEntity>(sql, parameters);

        }

        public BannerEntity GetEntity(long bannerId)
        {
            string sql = string.Format("{0} WHERE A.BannerId=?BannerId LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BannerId", bannerId, DbType.Int64, size: 20);
            return this.databaseContext.Slave.QueryFirstOrDefault<BannerEntity>(sql, parameters);
        }

        public BannerEntity Update(BannerEntity bannerEntity)
        {
            string sql = @"UPDATE Banner SET
                                Name=?Name,
                                BackgroundImage=?BackgroundImage,
                                ForegroundImage=?ForegroundImage,
                                Title=?Title,
                                Description=?Description,
                                Url=?Url,
                                BannerTagcode=?BannerTagcode
                            WHERE BannerId=?BannerId;
                            {0} WHERE A.BannerId=?BannerId LIMIT 1;";
            sql = string.Format(sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BannerId", bannerEntity.BannerId, DbType.Int64, size: 20);
            parameters.Add("?Name", bannerEntity.Name, DbType.AnsiString, size: 50);
            parameters.Add("?BackgroundImage", bannerEntity.BackgroundImage, DbType.AnsiString, size: 1000);
            parameters.Add("?ForegroundImage", bannerEntity.ForegroundImage, DbType.AnsiString, size: 1000);
            parameters.Add("?Title", bannerEntity.Title, DbType.AnsiString, size: 20);
            parameters.Add("?Description", bannerEntity.Description, DbType.AnsiString, size: 100);
            parameters.Add("?Url", bannerEntity.Url, DbType.AnsiString, size: 1000);
            parameters.Add("?BannerTagcode", bannerEntity.BannerTagcode, DbType.AnsiString, size: 50);
            return this.databaseContext.Master.QueryFirstOrDefault<BannerEntity>(sql, parameters, databaseContext.Transaction);
        }

        public bool CheckBannerTagcodeInUse(string bannerTagcode)
        {
            string sql = @"SELECT 1 FROM Banner WHERE BannerTagcode=?BannerTagcode LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BannerTagcode", bannerTagcode, DbType.AnsiString, size: 50);
            object result = this.databaseContext.Slave.ExecuteScalar(sql,parameters);
            return result != null;
        }

        public IEnumerable<BannerEntity> GetPageableList(string name, string bannerTagcode, out int dataCount, bool? ascending, int pageIndex = 1, int pageSize = 20)
        {
            int index = (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize;
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(name))
            {
                wheres.Add("A.Name=?Name");
                parameters.Add("?Name", name, DbType.AnsiString, size: 50);
            }

            if (!string.IsNullOrWhiteSpace(bannerTagcode))
            {
                wheres.Add("A.BannerTagcode=?BannerTagcode");
                parameters.Add("?BannerTagcode", bannerTagcode, DbType.AnsiString, size: 50);
            }
            string sql = string.Format(@"{0} {1} ORDER BY BannerId {4} LIMIT {2},{3}; 
                               SELECT Count(1) FROM Banner {1}",
                 QUERY,
                 wheres.Count > 0 ? string.Format(" WHERE {0}", string.Join(" AND ", wheres)) : string.Empty,
                 (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                 pageSize,
                 ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            var result = this.databaseContext.Slave.QueryMultiple<BannerEntity, int>(sql, parameters);

            dataCount = result.Item2;

            return result.Item1;
        }

        #region 检查Banner分类是否存在
        public async Task<bool> Exists(long bannerId)
        {
            string sql = string.Format(@"{0} WHERE BannerId=?BannerId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BannerId", bannerId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 启用Banner分类
        public async Task<bool> Enabled(long bannerId)
        {
            string sql = @"UPDATE banner SET Enabled=1 WHERE BannerId=?BannerId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BannerId", bannerId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 禁用Banner分类
        public async Task<bool> Disabled(long bannerId)
        {
            string sql = @"UPDATE banner SET Enabled=0 WHERE BannerId=?BannerId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?BannerId", bannerId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion
    }
}
